<?php
require_once('SQL.php');

class Joob_Db_Adapter{
	
		# Function: createTable(&$query)
		# ------------------------------------------
		# Setup a new database. The parameter must be the complete MySQL query that
		# is used to create database.
		
		public static function createTable($query)
		{
			SQL::connected();
			$result=SQL::query($query);
			if ($result){
				self::log("Create table successfully.\n\tQ: $query");
			}
			else{
				self::log("[Error] Create table unsuccessfully.\n\tQ: $query");
			}
		}
		
		
		# Function: clearTable(&$db)
		# -------------------------------------------
		# Clear (or truncate) all rows in the table, making it completely blank. 
		
		public static function clearTable($table)
		{
			confirm(Joob_Utils_Validator::alpha_num_data($table));
			SQL::connected();
			$query="truncate table `{$table}`";
			$result=SQL::query($query);
			if ($result){
				self::log("Truncate table $table successfully.\n\tQ: $query");
			}
			else{
				self::log("[Error] Truncate table $table unsuccessfully.\n\tQ: $query");
			}
		}
		
		
		/**
		 * @desc Return a list of all tables in the db.
		 */
		public static function tableList(){
			$list=array();
			SQL::connected();
			$query="show tables";
			$result=SQL::get($query);
			if ($result==null) return array();
			while ($row=SQL::fetch($result)){
				foreach ($row as $k=>$value){
					$list[]=$value;
				}
			}
			return $list;
		}
		
		
		
		# Function: exists($table)
		# ---------------------------------------------
		# Check if the current table exists in the database. Return true 
		# if it is, false otherwise.
		
		public static function exists($table)
		{
			confirm(Joob_Utils_Validator::alpha_num_data($table));
			SQL::connected();
			$query="show tables";
			$result=SQL::get($query);
			if ($result==null) return false;
			while ($row=SQL::fetch($result)){
				foreach ($row as $k=>$value){
					if (Joob_Utils_Word::icmp($value,$table)) return true;
				}
			}
			return false;
		}
		
		
		# Function: tableinfo()
		# ---------------------------------------------
		# Get the information about the table. It includes detailed structure of the
		# table. After processed, it will save information into the file databaselogs.txt.
		
		public static function tableinfo($table,$display=false){
			confirm(Joob_Utils_Validator::alpha_num_data($table));
			$query="describe $table";
			$result=SQL::to_array(SQL::get($query));
			
			$logs=self::serialize_db_info($result,"Table {$table}");			
			self::log("[{$table}] $logs",'info.txt');
			
			$c_query="show create table {$table}";
			$c_result=SQL::single($c_query);
			self::log("Creation query of {$table}:\n\t{$c_result['Create Table']}",'info.txt');
			
			if ($display==true)
			{
				echo "<pre><b>Table Info</b>\n\t".self::serialize_db_info($result,"Table <b>{$table}</b>");
				echo "\n\n<b>Creation Query</b>:\n\t{$c_result['Create Table']}\n</pre>";
			}
		}
		
			
		# Function: addColumn($table,$name,$type)
		# -------------------------------------------
		# Add a column (at the end) to the current table. The parameter must give the name of
		# the column to be added and its type. After changing, the logs will be written.	
		
		public static function addColumn($table, $name, $type)
		{
			confirm(Joob_Utils_Validator::alpha_num_data($table));
			$query="alter table $table add `$name` $type";
			$result=SQL::query($query);
			if ($result==true){
				self::log("Column $name added to $table.\n\tQ: $query","{$table}.txt");
				return true;
			}
			else{
				self::log("[Error] Column $name cannot be added to $table.\n\tQ: $query","error.txt");
				return false;
			}
		}
		
		
		# Function: removeColumn($table,$name)
		# -------------------------------------------
		# Remove a column (at the end) to the current table. The parameter must give the name of
		# the column to be removed. After changing, the logs will be written.	
		
		public function removeColumn($table,$name)
		{
			confirm(Joob_Utils_Validator::alpha_num_data($table));
			$query="alter table $table drop `$name`";
			$result=SQL::query($query);
			
			if ($result==true){
				self::log("Column $name removed from $table.\n\tQ: $query","{$table}.txt");
				return true;
			}
			else{
				self::log("[Error] Column $name cannot be removed from $table.\n\tQ: $query","error.txt");
				return false;
			}
		}
		
		
		# Function: alterColumn()
		# -------------------------------------------
		# Alter a column of the table by changing its type and save logs.
		
		public function alterColumn($table,$name,$new_name,$type){
			confirm(Joob_Utils_Validator::alpha_num_data($table));
			$query="alter table $table change $name $new_name $type";
			$result=SQL::query($query);
			if ($result==true){
				self::log("Change $name to $new_name.\n\tQ: $query","{$table}.txt");
				return true;
			}
			else{
				self::log("[Error] Cannot change $name to $new_name.\n\tQ$query","error.txt");
				return false;
			}
		}
		
		
		
		# Private Function: serialize_db_info()
		# ----------------------------------------------
		# Serialize the table info obtained by tableinfo() so that information can be saved.

		
		private static function serialize_db_info(&$info,$db){
			$result="$db has the following properties:";
			for ($i=0; $i<count($info); $i++)
			{
				$s=$info[$i];
				$field="\n\t\t{$s['Field']} has the following properties:";
				foreach ($s as $key=>$value)
				{
					$field=$field."\n\t\t\t{$key}: {$value}";
				}
				$result=$result.$field;
			}
			return $result;
		}
	
}